using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Net.Mail;
using System.Runtime.InteropServices;
using DataSync.DataAccess.Attendance;
using DataSync.Properties;
using DataSync.Services.Common;
using DataSync.Services.Staff;
using DataSync.Utilities;
using Microsoft.Office.Interop.Excel;
using DataTable=System.Data.DataTable;

namespace DataSync.Services.Attendance
{
    public class AttendanceService
    {
        private readonly AttendanceDataAccess _AttendanceDataAccess;

        public AttendanceService()
        {
            _AttendanceDataAccess = new AttendanceDataAccess();
        }

        public DataTable GetAttendances(int serverIdentification)
        {
            return _AttendanceDataAccess.GetAttendances(serverIdentification);
        }

        public DataTable GetAttendances(DateTime fromDate, DateTime toDate, int departmentId)
        {
            return _AttendanceDataAccess.GetAttendances(fromDate, toDate, departmentId);
        }

        public bool MoveAttendances(int serverIdentification)
        {
            bool actionResult;
            var dataTable = GetAttendances(serverIdentification);
            if (dataTable == null)
                return false;

            if (dataTable.Rows == null)
                return false;

            CommonService.SaveLog(Resources.OperationSynchronizeAttendance, Resources.MsgGetAttendanceSuccess + serverIdentification);
            foreach (DataRow row in dataTable.Rows)
            {
                if(row == null)
                    continue;

                actionResult = _AttendanceDataAccess.InsertAttendance(row);
                if (actionResult) 
                    continue;

                CommonService.SaveLog(Resources.OperationSynchronizeAttendance, Resources.MsgInsertAttendanceError + serverIdentification);
                return false;
            }

            CommonService.SaveLog(Resources.OperationSynchronizeAttendance, Resources.MsgInsertAttendanceSuccess + serverIdentification);
                                
            actionResult = _AttendanceDataAccess.DeleteAttendances(serverIdentification);
            if(actionResult)
                CommonService.SaveLog(Resources.OperationSynchronizeAttendance, Resources.MsgDeleteAttendanceSuccess + serverIdentification);
            else
                CommonService.SaveLog(Resources.OperationSynchronizeAttendance, Resources.MsgDeleteAttendanceError + serverIdentification);
            
            return true;
        }

        public bool GenerateReport(DataTable departmentList)
        {
            if (departmentList == null)
                return false;

            if (departmentList.Rows.Count == 0)
                return false;

            var fromDate = DateTimeHelper.StartDayOfWeek();
            var toDate = fromDate.AddDays(6);

            foreach (DataRow dataRow in departmentList.Rows)
            {
                if (dataRow == null)
                    return false;

                var departmentId = dataRow[Resources.DepartmentId].ToString();
                if (string.IsNullOrEmpty(departmentId))
                    return false;

                var departmentName = dataRow[Resources.DepartmentName].ToString();
                if (string.IsNullOrEmpty(departmentName))
                    return false;

                var staffService = new StaffService();
                var staffList = staffService.GetStaffs(Int32.Parse(departmentId));

                if (staffList == null)
                    return false;

                var reportFileName =
                    System.Windows.Forms.Application.StartupPath + @"\" + departmentName + Resources.ExcelExtension;

                //Create report file
                if (CreateReportFile(staffList, reportFileName, fromDate, toDate))
                {
                    CommonService.SaveLog(
                        Resources.OperationGenerateReport, 
                        Resources.MsgGenerateReportSuccess + ". Report name: " + reportFileName);
                    //return true;
                }
                else
                {
                    CommonService.SaveLog(
                        Resources.OperationGenerateReport,
                        Resources.MsgGenerateReportError + ". Report name: " + reportFileName);
                    return false;
                }
            }

            return true;
        }

        public bool SendMail(DataTable departmentList)
        {
            if (departmentList == null)
                return false;

            if (departmentList.Rows.Count == 0)
                return false;

            var smtpHost = ConfigurationManager.AppSettings[Resources.AppSettingSmtpHost];
            var expeditorAddress = ConfigurationManager.AppSettings[Resources.AppSettingExpeditorAddress];

            //Create mail instance
            var mailMessage =
                new MailMessage
                    {
                        IsBodyHtml = true,
                        From = new MailAddress(expeditorAddress),
                        Subject = Resources.ParamMailSubject,
                        Body = Resources.ParamMailBody
                    };

            //Smtp
            var smtpClient = new SmtpClient(smtpHost);
                //new SmtpClient
                //{
                //    Credentials = new System.Net.NetworkCredential("site.pse@gmail.com", "1234567890@"),
                //    Port = 587,
                //    Host = smtpHost,
                //    EnableSsl = true
                //};

            //Sending process
            foreach (DataRow dataRow in departmentList.Rows)
            {
                if (dataRow == null)
                    return false;

                var sendTo = dataRow[Resources.DepartmentContactEmail].ToString();
                if (string.IsNullOrEmpty(sendTo))
                    return false;

                var departmentName = dataRow[Resources.DepartmentName].ToString();
                if (string.IsNullOrEmpty(departmentName))
                    return false;

                var reportFileName =
                    System.Windows.Forms.Application.StartupPath + @"\" + departmentName + Resources.ExcelExtension;

                var fileInfo = new FileInfo(reportFileName);
                if (fileInfo.Exists)
                {
                    //Attachment
                    mailMessage.Attachments.Add(new Attachment(reportFileName));
                }

                //Receiptient
                var mailAddress = new MailAddress(sendTo);
                mailMessage.To.Add(mailAddress);

                //Send mail
                smtpClient.Send(mailMessage);
                CommonService.SaveLog(Resources.OperationSendReport, Resources.MSgSendReportSuccess + sendTo);

                //Clear mail parameters
                mailMessage.To.Clear();
                mailMessage.Attachments.Clear();
            }

            //Mail successfully sent to all receiptients
            return true;
        }

        private bool CreateReportFile(DataTable staffList, string reportFileName, DateTime fromDate, DateTime toDate)
        {
            if(staffList == null)
                return false;

            var excelVersion = CommonService.DetectExcelVersion();
            if (excelVersion == 0)
                return false;

            var fileInfo = new FileInfo(reportFileName);
            if(fileInfo.Exists)
                fileInfo.Delete();

            fileInfo = new FileInfo(
                System.Windows.Forms.Application.StartupPath + @"\" + Resources.ReportFileName);
            if (!fileInfo.Exists)
                return false;
            
            fileInfo.CopyTo(reportFileName);

            var excelApplication = new Application();
            var _WorkBook = excelApplication.Workbooks.Open(
                reportFileName,
                0,
                false,
                5,
                "",
                "",
                true,
                XlPlatform.xlWindows,
                "",
                true,
                false,
                0,
                true,
                false,
                false);

            var accpetableLate = 0;
            var workSheet = (Worksheet)_WorkBook.Worksheets[Resources.ReportSheet];
            var startIndex = 8;
            var oRange = workSheet.get_Range("A" + startIndex, "A" + startIndex);
            oRange.Value2 = "From: " + fromDate.ToShortDateString() + " To: " + toDate.ToShortDateString();

            startIndex = 12;
            foreach(DataRow dataRow in staffList.Rows)
            {
                //Identification number
                int staffId;
                Int32.TryParse(dataRow[Resources.EmployeeId].ToString(), out staffId);
                oRange = workSheet.get_Range("A" + startIndex, "A" + startIndex);                
                oRange.Value2 = staffId;
                //Nname
                oRange = workSheet.get_Range("B" + startIndex, "B" + startIndex);
                oRange.Value2 = dataRow[Resources.EmployeeName];
                //Sex
                oRange = workSheet.get_Range("C" + startIndex, "C" + startIndex);
                oRange.Value2 = dataRow[Resources.EmployeeSex];
                //Position
                oRange = workSheet.get_Range("D" + startIndex, "D" + startIndex);
                oRange.Value2 = dataRow["position_title"];

                var colLetter = 68;
                for (var counter = 0; counter < 6; counter++)
                {
                    var startDate = fromDate.AddDays(counter);
                    var resultStr = string.Empty;
                    var dataTable = _AttendanceDataAccess.GetPublicHoliday(startDate);
                    //Skip if holliday
                    if (dataTable.Rows.Count != 0)
                    {
                        colLetter += 2;
                        continue;
                    }
                    //Leave
                    dataTable = _AttendanceDataAccess.GetAuthorizedLeave(startDate, staffId);
                    if (dataTable.Rows.Count != 0)
                    {
                        resultStr = "A(P)";
                        //Morning
                        colLetter += 1;
                        oRange = workSheet.get_Range(((char)colLetter).ToString() + startIndex, ((char)colLetter).ToString() + startIndex);
                        oRange.Value2 = resultStr;
                        //Afternoon
                        colLetter += 1;
                        oRange = workSheet.get_Range(((char)colLetter).ToString() + startIndex, ((char)colLetter).ToString() + startIndex);
                        oRange.Value2 = resultStr;
                        continue;
                    }
                    
                    //Morning
                    //In
                    var shiftColumn = GetWorkingShiftColumn("m", StringHelper.Left(startDate.DayOfWeek.ToString(), 3), "in");
                    dataTable = _AttendanceDataAccess.CalculateTimeInMorning(startDate, staffId, shiftColumn);
                    if (dataTable.Rows.Count == 0)
                        resultStr = "A";
                    else
                    {
                        var calculationResult = dataTable.Rows[0][0].ToString();
                        var difference = DateTimeHelper.TimeToMinute(calculationResult, 1);
                        if (difference > accpetableLate)
                            resultStr = "L:" + difference;
                        //Out
                        shiftColumn = GetWorkingShiftColumn("m", StringHelper.Left(startDate.DayOfWeek.ToString(), 3), "out");
                        dataTable = _AttendanceDataAccess.CalculateTimeOutMorning(startDate, staffId, shiftColumn);
                        if (dataTable.Rows.Count != 0)
                        {
                            calculationResult = dataTable.Rows[0][0].ToString();
                            difference = DateTimeHelper.TimeToMinute(calculationResult, -1);
                            if (!string.IsNullOrEmpty(resultStr))
                                resultStr += "|";
                            resultStr += "E:" + difference;   
                        }
                    }
                    colLetter += 1;
                    oRange = workSheet.get_Range(((char)colLetter).ToString() + startIndex, ((char)colLetter).ToString() + startIndex);
                    oRange.Value2 = resultStr;

                    //Afternoon
                    if (counter >= 5)
                    {
                        //Saturday
                        continue;
                    }

                    resultStr = string.Empty;
                    //In
                    shiftColumn = GetWorkingShiftColumn("a", StringHelper.Left(fromDate.DayOfWeek.ToString(), 3), "in");
                    dataTable = _AttendanceDataAccess.CalculateTimeInAfternoon(startDate, staffId, shiftColumn);
                    if (dataTable.Rows.Count == 0)
                        resultStr = "A";
                    else
                    {
                        var calculationResult = dataTable.Rows[0][0].ToString();
                        var difference = DateTimeHelper.TimeToMinute(calculationResult, 1);
                        if (difference > accpetableLate)
                            resultStr = "L:" + difference;
                        //Out
                        shiftColumn = GetWorkingShiftColumn("a", StringHelper.Left(startDate.DayOfWeek.ToString(), 3), "out");
                        dataTable = _AttendanceDataAccess.CalculateTimeOutAfternoon(startDate, staffId, shiftColumn);
                        if (dataTable.Rows.Count != 0)
                        {
                            calculationResult = dataTable.Rows[0][0].ToString();
                            difference = DateTimeHelper.TimeToMinute(calculationResult, -1);
                            if (!string.IsNullOrEmpty(resultStr))
                                resultStr += "|";
                            resultStr += "E:" + difference;
                        }                        
                    }

                    colLetter += 1;
                    oRange = workSheet.get_Range(((char) colLetter).ToString() + startIndex, ((char) colLetter).ToString() + startIndex);
                    oRange.Value2 = resultStr;
                }
                startIndex += 1;
            }

            _WorkBook.Save();
            _WorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            excelApplication.Quit();
            
            //Release memory used by Excel
            Marshal.ReleaseComObject(_WorkBook);
            Marshal.ReleaseComObject(excelApplication);
            GC.Collect();
            GC.WaitForPendingFinalizers();

            return true;
        }

        private static string GetWorkingShiftColumn(string dayPart, string dayWeek, string direction)
        {
            if(string.IsNullOrEmpty(dayWeek))
                return string.Empty;

            if (string.IsNullOrEmpty(dayPart))
                return string.Empty;

            if (string.IsNullOrEmpty(direction))
                return string.Empty;

            var shiftColumn = dayWeek + "_" + dayPart + "_" + direction;
            return shiftColumn;
        }
    }
}